/**
 * Copyright 2015 Software innovation and R & D center. All rights reserved.
 * File Name: SysLoginLogDao.java
 * Encoding UTF-8
 * Version: 0.0.1
 * History:	2015年1月22日
 */
package com.sirdc.modules.sys.dao;

import java.util.List;

import com.sirdc.modules.sys.databean.SysLoginLogDataBean;
import com.sirdc.modules.utils.ObjectUtils;
import net.huashitong.supervision.task.entity.TaskUnit;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.sirdc.modules.core.dao.GenericQuery;
import com.sirdc.modules.core.dao.StringDao;
import com.sirdc.modules.sys.entity.SysLoginLog;
import com.sirdc.modules.sys.filter.SysLoginLogFilter;
import com.sirdc.modules.utils.StringUtils;

/**
 * 
 */
@Repository
public class SysLoginLogDao extends StringDao<SysLoginLog> {
	
	private void queryCondition(SysLoginLogFilter filter, GenericQuery query){
		if (StringUtils.isNotBlank(filter.getStartLogoutDate()) && StringUtils.isBlank(filter.getEndLogoutDate())) {
			query.append(" or tb.logoutDate >= :logoutDate");
			query.setParam("logoutDate", filter.getStartLogoutDate());
		}
		
		if (StringUtils.isBlank(filter.getStartLogoutDate()) && StringUtils.isNotBlank(filter.getEndLogoutDate())) {
			query.append(" or tb.logoutDate <= :logoutDate");
			query.setParam("logoutDate", filter.getEndLogoutDate());
		}
		
		if (StringUtils.isNotBlank(filter.getStartLogoutDate()) && StringUtils.isNotBlank(filter.getEndLogoutDate())) {
			
			query.append(" or tb.logoutDate between :startLogoutDate and :endLogoutDate");
			query.setParam("startLogoutDate", filter.getStartLogoutDate());
			query.setParam("endLogoutDate", filter.getEndLogoutDate());
		}
	}
	
	public List<SysLoginLog> query(final SysLoginLogFilter filter) {
		GenericQuery query = createQuery("tb");
		
		if (StringUtils.isNotBlank(filter.getUserName())) {
			query.append(" and tb.userName like :userName");
			query.setParam("userName", StringUtils.getLikewords(filter.getUserName()));
		}
		
		if (StringUtils.isNotBlank(filter.getUserTitlName())) {
			query.append(" and tb.userTitlName like :userTitlName");
			query.setParam("userTitlName", StringUtils.getLikewords(filter.getUserTitlName()));
		}
		
		if (StringUtils.isNotBlank(filter.getDeptName())) {
			query.append(" and tb.deptName like :deptName");
			query.setParam("deptName", StringUtils.getLikewords(filter.getDeptName()));
		}
		
		//``````````````````
		if (StringUtils.isNotBlank(filter.getStartLoginDate()) && StringUtils.isBlank(filter.getEndLoginDate())) {
			
			query.append(" and ( tb.loginDate >= :loginDate");
			query.setParam("loginDate", filter.getStartLoginDate());
			queryCondition(filter, query);
			query.append(")");
		}
		
		if (StringUtils.isBlank(filter.getStartLoginDate()) && StringUtils.isNotBlank(filter.getEndLoginDate())) {
			
			query.append(" and ( tb.loginDate <= :loginDate");
			query.setParam("loginDate", filter.getEndLoginDate());
			queryCondition(filter, query);
			query.append(")");
		}
		
		if (StringUtils.isNotBlank(filter.getStartLoginDate()) && StringUtils.isNotBlank(filter.getEndLoginDate())) {
			
			query.append(" and ( tb.loginDate between :startLoginDate and :endLoginDate");
			query.setParam("startLoginDate", filter.getStartLoginDate());
			query.setParam("endLoginDate", filter.getEndLoginDate());
			queryCondition(filter, query);
			query.append(")");
		}
		return query.listResult(filter);
	}
	
	/**
	 * 根据UserId获取登陆日志
	 * @param userId
	 * @return
	 */
	public SysLoginLog getByUserIdLoginDate(String userId, String date) {
		String hql = "from SysLoginLog log where log.userId=:userId and log.loginDate=:loginDate";
		GenericQuery query = create(hql);
		query.setParam("userId", userId);
		query.setParam("loginDate", date);
		return query.singleResult();
	}

	public int queryCount(SysLoginLogFilter filter) {
		GenericQuery query = createQuery("tb");

		if (StringUtils.isNotBlank(filter.getUserName())) {
			query.append(" and tb.userName like :userName");
			query.setParam("userName", StringUtils.getLikewords(filter.getUserName()));
		}

		if (StringUtils.isNotBlank(filter.getUserTitlName())) {
			query.append(" and tb.userTitlName like :userTitlName");
			query.setParam("userTitlName", StringUtils.getLikewords(filter.getUserTitlName()));
		}

		if (StringUtils.isNotBlank(filter.getDeptName())) {
			query.append(" and tb.deptName like :deptName");
			query.setParam("deptName", StringUtils.getLikewords(filter.getDeptName()));
		}

		//``````````````````
		if (StringUtils.isNotBlank(filter.getStartLoginDate()) && StringUtils.isBlank(filter.getEndLoginDate())) {

			query.append(" and ( tb.loginDate >= :loginDate");
			query.setParam("loginDate", filter.getStartLoginDate());
			queryCondition(filter, query);
			query.append(")");
		}

		if (StringUtils.isBlank(filter.getStartLoginDate()) && StringUtils.isNotBlank(filter.getEndLoginDate())) {

			query.append(" and ( tb.loginDate <= :loginDate");
			query.setParam("loginDate", filter.getEndLoginDate());
			queryCondition(filter, query);
			query.append(")");
		}

		if (StringUtils.isNotBlank(filter.getStartLoginDate()) && StringUtils.isNotBlank(filter.getEndLoginDate())) {

			query.append(" and ( tb.loginDate between :startLoginDate and :endLoginDate");
			query.setParam("startLoginDate", filter.getStartLoginDate());
			query.setParam("endLoginDate", filter.getEndLoginDate());
			queryCondition(filter, query);
			query.append(")");
		}
		return query.count();
	}

	public List<SysLoginLogDataBean> queryList(SysLoginLogFilter filter) {
		StringBuilder sql = new StringBuilder();
		sql.append( "SELECT L.sys_id as userId ,L.username as userName ,L.dept_sys_id as deptId,L.dept_name as deptName,L.role_id as roleId,LogNum.num as loginNum from ");
		sql.append( " ( SELECT login.*, dept.dept_name FROM t_sys_login login ,t_sys_dept as  dept WHERE 1 = 1 and login.dept_sys_id=dept.sys_id  ");
		if (StringUtils.isNotBlank(filter.getRoleId())){
			sql.append(" and role_id = '"+filter.getRoleId()+"'");
		}
		if (StringUtils.isNotBlank(filter.getDeptId())){
			sql.append(" and dept_id = '"+filter.getDeptId()+"'");
		}
		if (StringUtils.isNotBlank(filter.getUserName())){
			sql.append(" and username='"+filter.getUserName()+"'");
		}
		sql.append(" )L");
		sql.append(" left join  (select Log.* ,count(Log.sys_id) as num from t_sys_login_log Log where 1=1");

		if (StringUtils.isNotBlank(filter.getStartLoginDate()) && StringUtils.isBlank(filter.getEndLoginDate())) {
			sql.append(" and Log.login_date >= '"+filter.getStartLoginDate()+"'");
		}

		if (StringUtils.isBlank(filter.getStartLoginDate()) && StringUtils.isNotBlank(filter.getEndLoginDate())) {

			sql.append(" and Log.login_date <= '"+filter.getEndLoginDate()+"'");

		}

		if (StringUtils.isNotBlank(filter.getStartLoginDate()) && StringUtils.isNotBlank(filter.getEndLoginDate())) {

			sql.append(" and Log.login_date between '"+filter.getStartLoginDate()+"' and '"+filter.getEndLoginDate()+"'");
		}
		sql.append(" GROUP BY user_name ) as LogNum on L.username= LogNum.user_name");

		// 设置查询总条数,用于分页
		if (ObjectUtils.isNotBlank(filter.getPaging())) {

			int resultSize = this.countLoginLog(filter);
			filter.getPaging().setRecords(resultSize);
			//设置排序
			if (ObjectUtils.isNotBlank(filter.getPaging())) {
				if (StringUtils.isBlank(filter.getPaging().getSortCol())) {
					filter.getPaging().setSortCol("LogNum.num");
					filter.getPaging().setSortVal("desc");
				}


				sql.append(" order by " + filter.getPaging().getSortCol() + " " + filter.getPaging().getSortVal());
			}

			SQLQuery query = getSession().createSQLQuery(sql.toString());

			query.setFirstResult(filter.getPaging().getPageSize() * (filter.getPaging().getCurrentPage() - 1));
			query.setMaxResults(filter.getPaging().getPageSize());
			query.setResultTransformer(Transformers.aliasToBean(SysLoginLogDataBean.class));
			List<SysLoginLogDataBean> list = query.list();
			return list;
		} else {
			SQLQuery query = getSession().createSQLQuery(sql.toString());
			query.setResultTransformer(Transformers.aliasToBean(SysLoginLogDataBean.class));
			List<SysLoginLogDataBean> list = query.list();
			return list;
		}
	}

	private int countLoginLog(SysLoginLogFilter filter) {
		StringBuilder sql = new StringBuilder();
		sql.append( "SELECT count(L.username)  from ");
		sql.append( " (select * FROM t_sys_login where 1=1 ");
		if (StringUtils.isNotBlank(filter.getRoleId())){
			sql.append(" and role_id = '"+filter.getRoleId()+"'");
		}
		if (StringUtils.isNotBlank(filter.getDeptId())){
			sql.append(" and dept_id = '"+filter.getDeptId()+"'");
		}
		if (StringUtils.isNotBlank(filter.getUserName())){
			sql.append(" and username='"+filter.getUserName()+"'");
		}
		sql.append(" )L");
		sql.append(" left join  (select Log.* ,count(Log.sys_id) num from t_sys_login_log Log where 1=1");

		if (StringUtils.isNotBlank(filter.getStartLoginDate()) && StringUtils.isBlank(filter.getEndLoginDate())) {
			sql.append(" and Log.login_date >= '"+filter.getStartLoginDate()+"'");
		}

		if (StringUtils.isBlank(filter.getStartLoginDate()) && StringUtils.isNotBlank(filter.getEndLoginDate())) {

			sql.append(" and Log.login_date <= '"+filter.getEndLoginDate()+"'");

		}

		if (StringUtils.isNotBlank(filter.getStartLoginDate()) && StringUtils.isNotBlank(filter.getEndLoginDate())) {

			sql.append(" and Log.login_date between '"+filter.getStartLoginDate()+"' and '"+filter.getEndLoginDate()+"'");
		}
		sql.append(" ) as LogNum on L.username= LogNum.user_name");
		SQLQuery query = getSession().createSQLQuery(sql.toString());
		return ((Number) query.uniqueResult()).intValue();
	}
}